














ALTER               proc sp_wip_inventory_all_report
-- Input parameter is process StepID;
@process_step_id int,
@process_step_type varchar(15),
@date_From varchar(10),
@date_To   varchar(10)


as

/*variable to store the values
*/
declare @qty_in int, @qty_out int, @yield float
declare @date_in datetime, @date_out datetime
declare @temp_lot_id int


if @process_step_type='Mark and Pack'
begin
set @process_step_type = 'Mark & Pack'
end


declare productcursor CURSOR FOR
SELECT inv.ProcessStep_ID ProcessStep_ID FROM Inventory_Step_Table inv ,Product,Process_Step  where (Process_Step.Ps_Type = 'Finished Goods' or Process_Step.Ps_Type = 'Die bank') and inv.ProcessStep_ID = product.ProcessStep_ID and inv.ProcessStep_ID = Process_Step.ProcessStep_ID
open productcursor
fetch next from productcursor into @process_step_id
while @@fetch_status = 0
begin
declare @lotCursor CURSOR
declare flowCursor CURSOR FOR
select * from fn_get_process_flow_by_id(@process_step_id)

declare @@temp_process_step_id bigint
declare @@temp_ps_type varchar(20)
declare @@temp_ps_type_name varchar(20)
open flowCursor 
fetch next from flowCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name

while @@fetch_status = 0
begin
-- Cursor to store the Lot_Number and Lot_ID
-- If process_step_id variable is 0 store all lot_numbers and 
set @lotCursor = CURSOR for
select distinct Lot_ID, Lot_Number,ProcessStep_ID from Lot_Table where ProcessStep_ID = @@temp_process_step_id and (Lot_Current_Status = 'Running' or  Lot_Current_Status='Queue'  or  Lot_Current_Status='HOLD')

set @@temp_process_step_id =null


declare @lot_no varchar(50)
declare @lot_id int
declare @running_process_id int
declare @lot_process_id int
declare @running_lot_id int
declare @running_lot_no varchar(50)
declare @results Table(Lot_ID varchar(20),Location varchar(50),Product varchar(50), Process_Name varchar(50), Qty_in int, Qty_out int, Date_in datetime, Date_out datetime,Unit_Cost float,PS_Type varchar(50),Product_Type varchar(50), Lot_Status varchar(50))

open @lotCursor 
fetch next from @lotCursor
into @lot_id, @lot_no,@lot_process_id

while @@fetch_status = 0
begin
	
	print @lot_no
	print @lot_id
	print @lot_process_id

	declare @procstepCursor CURSOR
	--select @running_process_id=ProcessStep_ID,@running_lot_id=Lot_ID,@running_lot_no=Lot_Number  from Lot_Table where ProcessStep_ID in (select ProcessStep_ID from fn_get_process_flow_by_id(@process_step_id)) and  Lot_Number = @lot_no and (Lot_Current_Status = 'Running' or  Lot_Current_Status='Queue')
	select @running_process_id=ProcessStep_ID,@running_lot_id=Lot_ID,@running_lot_no=Lot_Number  from Lot_Table where Lot_Number = @lot_no and Lot_Current_Status = 'Running'

	--print '1 @running_process_id = '
	--print @running_process_id
	
	--print '1 @running_lot_no = '
	--print @running_lot_no



	if(@process_step_type='0')
	begin
	set @procstepCursor = CURSOR for
	select fn_get_process_flow_backward_by_id.ProcessStep_ID,Lot_ID from fn_get_process_flow_backward_by_id(@running_process_id),Lot_Table where lot_table.lot_Number=@running_lot_no  and lot_table.ProcessStep_ID=fn_get_process_flow_backward_by_id.ProcessStep_ID order by temp_count desc	
	end
	
	else
	begin
	set @procstepCursor = CURSOR for	
	select fn_get_process_flow_backward_by_id.ProcessStep_ID,Lot_ID from fn_get_process_flow_backward_by_id(@running_process_id),Lot_Table where lot_table.lot_Number=@running_lot_no and lot_table.ProcessStep_ID=fn_get_process_flow_backward_by_id.ProcessStep_ID and fn_get_process_flow_backward_by_id.Process_Type_Name like @process_step_type and (Lot_Current_Status = 'Running' or  Lot_Current_Status='Queue') order by temp_count desc	
	end

	declare @type varchar(50)
	declare @testType varchar(50)
	declare @location varchar(50)
	declare @product varchar(50)
        declare @product_type varchar(50)
	declare @temp_step int
	declare @standardUnitCost float
        open @procstepCursor
	fetch next from @procstepCursor into @temp_step,@temp_lot_id
	while @@fetch_status = 0
	begin
		select @type = PS_Type_Name,@testType=PS_Type from Process_Step where ProcessStep_ID = @temp_step
		select @location = (select L_Vendor_Name from location,lot_table where lot_id=@temp_lot_id and lot_table.location_Id = location.location_Id)
		select @product =(select part_number from product where  processStep_id = @temp_step)
                select @product_type =(select P_Type from product where  processStep_id = @temp_step)
		
		--select @type = PS_Type_Name,@testType=PS_Type from Process_Step where ProcessStep_ID = @temp_step
		--select @location = (select L_Vendor_Name from location,lot_table where lot_id=@temp_lot_id and lot_table.location_Id = location.location_Id)
		--select @product =(select part_number from product where  processStep_id = @temp_step)
		
		--print 'Type = '+@type
		if @type = 'Finished Goods' or @type ='Wafer Store' or @type = 'Mark & Pack' or @type='Die Bank'
		begin
			declare @temp_current_status varchar (50)
			select @qty_in = IL_Qty_In, @qty_out =IL_Qty_Out,  @date_in = IL_Date_In ,  @date_out = IL_Date_Out from Inventory_Lot where Lot_ID = @temp_lot_id
			select @temp_current_status = Lot_Current_Status from Lot_Table where Lot_ID = @temp_lot_id
			insert into @results(Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out, Date_in, Date_out,Product_Type, Lot_Status) values(@lot_no,@location,@product, @type, @qty_in, @qty_out, @date_in, @date_out, @product_type,@temp_current_status) 
		end
		set @qty_in=null
		set @qty_out=null
		set @date_in=null
		set @date_out=null
                set @product_type = null
		set @temp_current_status = null			
		fetch next from @procstepCursor into @temp_step,@temp_lot_id
	end
	close @procstepCursor
	deallocate @procstepCursor
	set @running_lot_no = null
	set @running_process_id = 0
	set @type = null
	set @standardUnitCost=null	
	fetch next from @lotCursor
	into @lot_id, @lot_no,@lot_process_id
end
close @lotCursor
deallocate @lotCursor
fetch next from flowCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name
end
close flowCursor
deallocate flowCursor
fetch next from productcursor into @process_step_id
end 
close productcursor
deallocate productcursor


if(@date_From = '0' and @date_To = '0')
begin
select distinct Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out, Date_in, Date_out,Product_Type,Lot_Status from @results where Lot_Status = 'Running' order by Product_Type
end
else 
begin
select distinct Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out, Date_in, Date_out,Product_Type, Lot_Status from @results where Lot_Status = 'Running' and Date_In>=@date_From and Date_In<=@date_To order by Product_Type
end














